package ohmel;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.*;
import java.sql.Statement;

public final class Page extends HttpServlet
{
  /**
	 * 
	 */
	private static final long serialVersionUID = -2299504620802640136L;

protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException
  {
    sendPage(req, res);
  }

  @SuppressWarnings("deprecation")
protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException
  {
    if(req.getParameter("list") != null)
    {
    	try
    	{  
    		PrintWriter o = res.getWriter();
    		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 	
    		Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");
    		
    		/** Creating Statement*/
    		Statement stmt = con.createStatement();
    		ResultSet rs=stmt.executeQuery("select * from student");
    		while(rs.next()) 
    		{
    			res.setContentType("text/html");
    			o.print("<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>");
    		    
    			o.print("<table>" +				      
    				      "<tr>" +
    				      "<td> \nName: " + rs.getString(1) + "</td>" +
    				      "<td> Address: " + rs.getString(2) + "</td>" +
    				      "<td> Phone: " + rs.getString(3) + "</td>" +
    				      "</tr>" +
    				      "</FORM></BODY></HTML></table");;
    		}
    	}
    	catch (Exception e) 
    	{
    		e.printStackTrace();
    	}
     
    }
    else if (req.getParameter("insert") != null)
    {
    	insertPage(req, res);
    }
    
    else if (req.getParameter("insertdata") != null)
    {
    	try
    	{  
    		PrintWriter o = res.getWriter();
    		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 	
    		Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");
    		
    		/** Creating Statement*/
    		String name = req.getParameter("name");
    		String phone = req.getParameter("phone");
    		String address = req.getParameter("address");
    		//Statement stmt = con.createStatement();
    		
    		PreparedStatement stmt = con.prepareStatement("insert into student(Name, Address, Phone) values("+ "'" + name + "'" + "," + "'" + address + "'" + ","+ "'" + phone + "'" + ")");
    		int re = stmt.executeUpdate();
    		
    		res.setContentType("text/html");
    	    res.setHeader("pragma", "no-cache");
    	    o.print("<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>");
    	    
    	      o.print("<FORM METHOD=POST>"+
    		      "<INPUT TYPE=SUBMIT NAME=\"list\" VALUE=\"list\">"+
    		      "</FORM></BODY></HTML>");
    	}
    	catch (Exception e) 
    	{
    		String phone = req.getParameter("phone");
    		PrintWriter o = res.getWriter();
    		o.println("Error statement");
    		o.print(phone);
    		e.printStackTrace();
    	}
    }
    
    else if (req.getParameter("delete") != null)
    {
    	deletePage(req, res);
    }
    
    else if (req.getParameter("deleteval") != null)
    {
    	try
    	{  
    		PrintWriter o = res.getWriter();
    		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 	
    		Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");
    		
    		/** Creating Statement*/
    		String delename = req.getParameter("delname");
    		//Statement stmt = con.createStatement();
    		
    		PreparedStatement st = con.prepareStatement("delete from student where name='" + delename + "'");
    		int pe = st.executeUpdate();
    		
    		res.setContentType("text/html");
    	    res.setHeader("pragma", "no-cache");
    	    o.print("<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>");
    	    
    	      o.print("<FORM METHOD=POST>"+
    		      "<INPUT TYPE=SUBMIT NAME=\"list\" VALUE=\"list\">"+
    		      "<INPUT TYPE=SUBMIT NAME=\"insertdata\" VALUE=\"insertdata\">"+
    		      "</FORM></BODY></HTML>");
    	}
    	catch (Exception e) 
    	{
    		String phone = req.getParameter("phone");
    		PrintWriter o = res.getWriter();
    		o.println("Error statement");
    		e.printStackTrace();
    	}
    }
    
    else if (req.getParameter("update") != null)
    {
    	updatePage(req, res);
    }
    else if (req.getParameter("nameupdate") != null)
    {
    	try
    	{  
    		PrintWriter o = res.getWriter();
    		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 	
    		Connection con = DriverManager.getConnection("jdbc:odbc:jdbctest", "admin", "admin");
    		
    		/** Creating Statement*/
    		String upname = req.getParameter("updatename");
    		String updname = req.getParameter("uname");
    		String upphone = req.getParameter("uphone");
    		String upaddress = req.getParameter("uaddress");
    		//Statement stmt = con.createStatement();
    		
    		PreparedStatement stm = con.prepareStatement("update student set name='" +updname+ "'" + ", " + "address='" +upaddress+ "'" + ", " + "phone='" +upphone+ "'" + "where name='" +upname+ "'");
    		int ne = stm.executeUpdate();
    		
    		res.setContentType("text/html");
    	    res.setHeader("pragma", "no-cache");
    	    o.print("<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>");
    	    
    	      o.print("<FORM METHOD=POST>"+
    		      "<INPUT TYPE=SUBMIT NAME=\"list\" VALUE=\"list\">"+
    		      "<INPUT TYPE=SUBMIT NAME=\"insertdata\" VALUE=\"insertdata\">"+
    		      "</FORM></BODY></HTML>");
    	}
    	catch (Exception e) 
    	{
    		String p = req.getParameter("uphone");
    		PrintWriter o = res.getWriter();
    		o.println("Error statement");
    		o.println(p);
    		e.printStackTrace();
    	}
    }
 }

  @SuppressWarnings("deprecation")
private void sendPage(HttpServletRequest req, HttpServletResponse res)
          throws ServletException, IOException
  {
    res.setContentType("text/html");
    res.setHeader("pragma", "no-cache");
    PrintWriter o = res.getWriter();
    o.print("<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>");
    
      o.print("<FORM METHOD=POST><b>student navigation</b> <br /> "+
	      "<INPUT TYPE=SUBMIT NAME=\"list\" VALUE=\"list\">"+
	      "<INPUT TYPE=SUBMIT NAME=\"insert\" VALUE=\"insert\">"+
	      "<INPUT TYPE=SUBMIT NAME=\"delete\" VALUE=\"delete\">"+
	      "<INPUT TYPE=SUBMIT NAME=\"update\" VALUE=\"update\">"+
	      "</FORM></BODY></HTML>");
   
    o.close();
  }
  
  private void insertPage(HttpServletRequest req, HttpServletResponse res)
  throws ServletException, IOException
  {
	res.setContentType("text/html");
	res.setHeader("pragma", "no-cache");
	PrintWriter o = res.getWriter();
	o.println(
				"<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>" +
				"<FORM METHOD=POST>" +
				"<b>Name</b> <INPUT TYPE=TEXT NAME=\"name\"><br />"+
				"<b>Address</b> <INPUT TYPE=TEXT NAME=\"address\"><br />"+
				"<b>Phone</b> <INPUT TYPE=TEXT NAME=\"phone\"><br />"+
				"<INPUT TYPE=SUBMIT NAME=\"insertdata\" VALUE=\"insertdata\">" +
				"</FORM></BODY></HTML>"
	
			);
  }
  
  private void deletePage(HttpServletRequest req, HttpServletResponse res)
  throws ServletException, IOException
  {
	res.setContentType("text/html");
	res.setHeader("pragma", "no-cache");
	PrintWriter o = res.getWriter();
	o.println(
				"<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>" +
				"<FORM METHOD=POST>" +
				"<b>Name</b> <INPUT TYPE=TEXT NAME=\"delname\">"+
				"<INPUT TYPE=SUBMIT NAME=\"deleteval\" VALUE=\"deleteval\">" +
				"</FORM></BODY></HTML>"
	
			);
  }
  private void updatePage(HttpServletRequest req, HttpServletResponse res)
  throws ServletException, IOException
  {
	res.setContentType("text/html");
	res.setHeader("pragma", "no-cache");
	PrintWriter o = res.getWriter();
	o.println(
				"<HTML><HEAD><TITLE>Exercise2.1</TITLE></HEAD><BODY>" +
				"<FORM METHOD=POST>" +
				"<b>Put the name you want to update</b> <br />  " +				
				"<b>Name</b> <INPUT TYPE=TEXT NAME=\"updatename\"><br /><br />"+
				"<b>Put the values you want to update</b> <br />  " +
				"<b>Name</b> <INPUT TYPE=TEXT NAME=\"uname\"><br />"+
				"<b>Address</b> <INPUT TYPE=TEXT NAME=\"uaddress\"><br />"+
				"<b>Phone</b> <INPUT TYPE=TEXT NAME=\"uphone\"><br />"+
				"<INPUT TYPE=SUBMIT NAME=\"nameupdate\" VALUE=\"nameupdate\">" +
				"</FORM></BODY></HTML>"
	
			);
  }
}